﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace ConfigDatabase
{
public    class Script
{
    public const string DatabaseScript =
        @"USE master
GO
if db_id('IgmDb') is not null DROP DATABASE IgmDb
GO
CREATE DATABASE IgmDb --InternGraduationManagementDb
GO
USE IgmDb
GO
CREATE TABLE [User] 
(
	Id int primary key identity(1,1),
	Permission int not null, -- 0 = Admin, 1 = TrK, 2 = TLDT
	Name nvarchar(50) unique not null, 
	[Password] nvarchar(80)not null
)
GO
CREATE TABLE Major
(
	Id int primary key identity(1,1),
	Code nvarchar(4) unique not null, -- THUD / CNPM
	Name nvarchar(50) unique not null -- Tin hoc ung dung
)
GO
CREATE TABLE KindIntern    
(
	Id int primary key identity(1,1),
	[Year] int not null,
	Name nvarchar(50) unique not null
)
GO
CREATE TABLE Student
(
	Id int primary key identity(1,1),
	Code nvarchar(8) unique not null, -- CQ080585
	Name nvarchar(50) not null, -- Le Thi Thanh Hai
	IdMajor int references Major(Id) not null,
	DateOfBirth datetime,
	Term bit not null, -- 0 : SV nam 3, 1: SV nam 4
)
GO
CREATE TABLE Lecturer 
(
	Id int primary key identity(1,1),
	IdMajor int references Major(Id) not null,
	Name nvarchar(50) not null,-- Ninh Thi Thanh Tam
	DateOfBirth datetime,
	UniversityDegree nvarchar(50) not null, -- Hoc vi; Cu nhan, Thac si, Tien si
)
GO
CREATE TABLE KindMark    
(
	Id int primary key identity(1,1),
	Name nvarchar(50) not null
)
GO
CREATE TABLE StudentMark    
(
	Id int primary key identity(1,1),
	IdKindMark int references KindMark(Id),
	IdStudent int references Student(Id),
	Mark float,
	IsDebt bit -- Con no mon khong 1 = Co
)
GO
CREATE TABLE [Group]   
(
	Id int primary key identity(1,1),
	IdKindIntern int references KindIntern(Id) not null,
	IdMajor int references Major(Id) not null,
	Name nvarchar(50) unique not null, -- Nhom1
	NumberMembers int not null,
	Project nvarchar(MAX)
)
GO
CREATE TABLE GroupStudent
(
	Id int primary key identity(1,1),
	IdGroup int references [Group](Id) not null,
	IdStudent int references Student(Id) not null,
	IsLeader bit not null -- 1: la nhom truong
)
GO
CREATE TABLE GroupLecturer
(
	Id int primary key identity(1,1),
	IdGroups int references [Group](Id) not null,
	IdLecturer int references Lecturer(Id) not null,
	[Year] int not null
)
GO
CREATE TABLE StudentGraduation   
(
	Id int primary key identity(1,1),
	IdStudent int references Student(Id) not null,
	Project nvarchar(MAX),
)
GO
CREATE TABLE StudentLecturer
(
	Id int primary key identity(1,1),
	IdStudent int references StudentGraduation(Id) not null,
	IdLecturer int references Lecturer(Id) not null,
	[Year] int not null
)
GO
CREATE TABLE Subcommittee
(
	Id int primary key identity(1,1),
	IdKindIntern int references KindIntern(Id) not null,
	IdMajor int references Major(Id) not null,
	Name nvarchar(50) unique not null, -- Nhom1
	NumberMembers int not null
)
GO
CREATE TABLE SubcommitteeLecturer
(
	Id int primary key identity(1,1),
	IdSubcommittee int references Subcommittee(Id) not null,
	IdLecturer int references Lecturer(Id) not null,
)
GO
CREATE TABLE SubcommitteeGroup
(
	Id int primary key identity(1,1),
	IdSubcommittee int references Subcommittee(Id) not null,
	IdGroup int references [Group](Id) not null,
)
GO
CREATE TABLE SubcommitteeStudent
(
	Id int primary key identity(1,1),
	IdSubcommittee int references Subcommittee(Id) not null,
	IdStudent int references StudentGraduation(Id) not null,
)
GO
CREATE TABLE LastModifyData -- It change for all userId in every where
(
	Id int primary key identity(1,1),
	IdUser int references [User](Id) not null,
	LastTime DateTime not null,
	ChangeLog nvarchar(250) not null,
)
GO
-----------------------------------------------------------------------------------------------------
-------------------------------------------- INSERT DATA --------------------------------------------
-----------------------------------------------------------------------------------------------------
INSERT INTO [dbo].[User]([Permission],[Name], [Password])VALUES(0,N'Admin','123456')
INSERT INTO [dbo].[User]([Permission],[Name], [Password])VALUES(1,N'TruongKhoa','123456')
INSERT INTO [dbo].[User]([Permission],[Name], [Password])VALUES(2,N'TroLy','123456')
GO
-----------------------------------------------------------------------------------------------------
INSERT INTO [dbo].[Major]([Code],[Name])VALUES('CNTT',N'Khoa học máy tính')
INSERT INTO [dbo].[Major]([Code],[Name])VALUES('QLGD',N'Tin học văn phòng')
INSERT INTO [dbo].[Major]([Code],[Name])VALUES('TLGD',N'Thiết kế hệ thống thông tin')
GO
-----------------------------------------------------------------------------------------------------
INSERT INTO [dbo].[KindIntern]([Year],[Name])VALUES(2012,N'Thực tập cơ sở')
INSERT INTO [dbo].[KindIntern]([Year],[Name])VALUES(2012,N'Thực tập tốt nghiệp')
INSERT INTO [dbo].[KindIntern]([Year],[Name])VALUES(2012,N'Khóa luận tốt nghiệp')
GO
-----------------------------------------------------------------------------------------------------
INSERT INTO [dbo].[Student]([Code],[Name],[IdMajor],[DateOfBirth],[Term])VALUES('CQ080501',N'Nguyễn Thị A',1,null,1)
INSERT INTO [dbo].[Student]([Code],[Name],[IdMajor],[DateOfBirth],[Term])VALUES('CQ080502',N'Trần Văn B',1,null,0)
INSERT INTO [dbo].[Student]([Code],[Name],[IdMajor],[DateOfBirth],[Term])VALUES('CQ080503',N'Lê Văn C',1,null,1)
INSERT INTO [dbo].[Student]([Code],[Name],[IdMajor],[DateOfBirth],[Term])VALUES('CQ080504',N'Hoàng Thị D',1,null,1)
INSERT INTO [dbo].[Student]([Code],[Name],[IdMajor],[DateOfBirth],[Term])VALUES('CQ080505',N'Phạm Văn E',1,null,0)
INSERT INTO [dbo].[Student]([Code],[Name],[IdMajor],[DateOfBirth],[Term])VALUES('CQ080506',N'Phan Thị F',1,null,1)
INSERT INTO [dbo].[Student]([Code],[Name],[IdMajor],[DateOfBirth],[Term])VALUES('CQ080507',N'Vũ Văn G',1,null,1)
INSERT INTO [dbo].[Student]([Code],[Name],[IdMajor],[DateOfBirth],[Term])VALUES('CQ080508',N'Đặng Thị H',1,null,0)
INSERT INTO [dbo].[Student]([Code],[Name],[IdMajor],[DateOfBirth],[Term])VALUES('CQ080509',N'Bùi Thị I',1,null,1)
INSERT INTO [dbo].[Student]([Code],[Name],[IdMajor],[DateOfBirth],[Term])VALUES('CQ080510',N'Đỗ Văn K',1,null,1)
INSERT INTO [dbo].[Student]([Code],[Name],[IdMajor],[DateOfBirth],[Term])VALUES('CQ080511',N'Hồ Thị L',1,null,0)
INSERT INTO [dbo].[Student]([Code],[Name],[IdMajor],[DateOfBirth],[Term])VALUES('CQ080512',N'Ngô Văn M',1,null,1)
INSERT INTO [dbo].[Student]([Code],[Name],[IdMajor],[DateOfBirth],[Term])VALUES('CQ080513',N'Dương Thị N',1,null,0)
INSERT INTO [dbo].[Student]([Code],[Name],[IdMajor],[DateOfBirth],[Term])VALUES('CQ080514',N'Đào Văn O',1,null,1)
INSERT INTO [dbo].[Student]([Code],[Name],[IdMajor],[DateOfBirth],[Term])VALUES('CQ080515',N'Đoàn Thị P',1,null,1)
INSERT INTO [dbo].[Student]([Code],[Name],[IdMajor],[DateOfBirth],[Term])VALUES('CQ080516',N'Lý Văn Q',1,null,0)
INSERT INTO [dbo].[Student]([Code],[Name],[IdMajor],[DateOfBirth],[Term])VALUES('CQ080517',N'Vương Thị U',1,null,1)
INSERT INTO [dbo].[Student]([Code],[Name],[IdMajor],[DateOfBirth],[Term])VALUES('CQ080518',N'Trịnh Văn X',1,null,1)
INSERT INTO [dbo].[Student]([Code],[Name],[IdMajor],[DateOfBirth],[Term])VALUES('CQ080519',N'Trương Thị Y',1,null,0)
INSERT INTO [dbo].[Student]([Code],[Name],[IdMajor],[DateOfBirth],[Term])VALUES('CQ080520',N'Đinh Văn T',1,null,1)
INSERT INTO [dbo].[Student]([Code],[Name],[IdMajor],[DateOfBirth],[Term])VALUES('CQ080521',N'Lâm Thị  A',1,null,1)
INSERT INTO [dbo].[Student]([Code],[Name],[IdMajor],[DateOfBirth],[Term])VALUES('CQ080522',N'Phùng Văn B',1,null,0)
INSERT INTO [dbo].[Student]([Code],[Name],[IdMajor],[DateOfBirth],[Term])VALUES('CQ080523',N'Mai Thị C',1,null,1)
INSERT INTO [dbo].[Student]([Code],[Name],[IdMajor],[DateOfBirth],[Term])VALUES('CQ080524',N'Tô Văn D',1,null,1)
INSERT INTO [dbo].[Student]([Code],[Name],[IdMajor],[DateOfBirth],[Term])VALUES('CQ080525',N'Trương Thị E',1,null,0)
INSERT INTO [dbo].[Student]([Code],[Name],[IdMajor],[DateOfBirth],[Term])VALUES('CQ080526',N'Hà Văn F',1,null,1)
INSERT INTO [dbo].[Student]([Code],[Name],[IdMajor],[DateOfBirth],[Term])VALUES('CQ080527',N'Triệu Thị G',1,null,1)
INSERT INTO [dbo].[Student]([Code],[Name],[IdMajor],[DateOfBirth],[Term])VALUES('CQ080528',N'Lưu Văn H',1,null,0)
INSERT INTO [dbo].[Student]([Code],[Name],[IdMajor],[DateOfBirth],[Term])VALUES('CQ080529',N'Quách Thị I',1,null,1)
INSERT INTO [dbo].[Student]([Code],[Name],[IdMajor],[DateOfBirth],[Term])VALUES('CQ080530',N'Cao Thị K',1,null,1)
INSERT INTO [dbo].[Student]([Code],[Name],[IdMajor],[DateOfBirth],[Term])VALUES('CQ080531',N'Chu Văn L',1,null,0)
INSERT INTO [dbo].[Student]([Code],[Name],[IdMajor],[DateOfBirth],[Term])VALUES('CQ080532',N'Vương Thị M',1,null,1)
INSERT INTO [dbo].[Student]([Code],[Name],[IdMajor],[DateOfBirth],[Term])VALUES('CQ080533',N'Châu Văn N',1,null,0)
INSERT INTO [dbo].[Student]([Code],[Name],[IdMajor],[DateOfBirth],[Term])VALUES('CQ080534',N'Từ Văn O',1,null,1)
INSERT INTO [dbo].[Student]([Code],[Name],[IdMajor],[DateOfBirth],[Term])VALUES('CQ080535',N'Lê Thị P',1,null,1)
INSERT INTO [dbo].[Student]([Code],[Name],[IdMajor],[DateOfBirth],[Term])VALUES('CQ080536',N'Nguyễn Văn Q',1,null,0)
INSERT INTO [dbo].[Student]([Code],[Name],[IdMajor],[DateOfBirth],[Term])VALUES('CQ080537',N'Trần Văn R',1,null,1)
INSERT INTO [dbo].[Student]([Code],[Name],[IdMajor],[DateOfBirth],[Term])VALUES('CQ080538',N'Đỗ Thị S',1,null,1)
INSERT INTO [dbo].[Student]([Code],[Name],[IdMajor],[DateOfBirth],[Term])VALUES('CQ080539',N'Phạm Thị Y',1,null,0)
INSERT INTO [dbo].[Student]([Code],[Name],[IdMajor],[DateOfBirth],[Term])VALUES('CQ080540',N'Dương Văn T',1,null,1)
GO
-----------------------------------------------------------------------------------------------------
INSERT INTO [dbo].[Lecturer]([IdMajor],[Name],[DateOfBirth],[UniversityDegree ])VALUES('1',N'Chưa có',null,N'Thạc sĩ')
INSERT INTO [dbo].[Lecturer]([IdMajor],[Name],[DateOfBirth],[UniversityDegree ])VALUES('1',N'Ninh Thị T',null,N'Thạc sĩ')
INSERT INTO [dbo].[Lecturer]([IdMajor],[Name],[DateOfBirth],[UniversityDegree ])VALUES('2',N'Trần Thị N',null,N'Cử nhân')
INSERT INTO [dbo].[Lecturer]([IdMajor],[Name],[DateOfBirth],[UniversityDegree ])VALUES('1',N'Phạm Quang T',null,N'Tiến sĩ')
INSERT INTO [dbo].[Lecturer]([IdMajor],[Name],[DateOfBirth],[UniversityDegree ])VALUES('1',N'Hoàng Phan A',null,N'Cử nhân')
INSERT INTO [dbo].[Lecturer]([IdMajor],[Name],[DateOfBirth],[UniversityDegree ])VALUES('2',N'Nguyễn Thị N',null,N'Thạc sĩ')
INSERT INTO [dbo].[Lecturer]([IdMajor],[Name],[DateOfBirth],[UniversityDegree ])VALUES('1',N'Lê Quang T',null,N'Thạc sĩ')
GO
-----------------------------------------------------------------------------------------------------
INSERT INTO [dbo].[KindMark]([Name])VALUES('K5')
INSERT INTO [dbo].[KindMark]([Name])VALUES('N3')
INSERT INTO [dbo].[KindMark]([Name])VALUES('TT')
INSERT INTO [dbo].[KindMark]([Name])VALUES('TN')
GO
-----------------------------------------------------------------------------------------------------
INSERT INTO [dbo].[StudentMark]([IdKindMark],[IdStudent],[Mark],[IsDebt])VALUES(1,1,6.8,0)
INSERT INTO [dbo].[StudentMark]([IdKindMark],[IdStudent],[Mark],[IsDebt])VALUES(1,2,7.7,0)
INSERT INTO [dbo].[StudentMark]([IdKindMark],[IdStudent],[Mark],[IsDebt])VALUES(1,3,5.4,0)
INSERT INTO [dbo].[StudentMark]([IdKindMark],[IdStudent],[Mark],[IsDebt])VALUES(1,4,6.5,0)
INSERT INTO [dbo].[StudentMark]([IdKindMark],[IdStudent],[Mark],[IsDebt])VALUES(1,5,7.5,0)
INSERT INTO [dbo].[StudentMark]([IdKindMark],[IdStudent],[Mark],[IsDebt])VALUES(1,6,6.6,0)
INSERT INTO [dbo].[StudentMark]([IdKindMark],[IdStudent],[Mark],[IsDebt])VALUES(1,7,7.5,0)
INSERT INTO [dbo].[StudentMark]([IdKindMark],[IdStudent],[Mark],[IsDebt])VALUES(1,8,5.1,1)
INSERT INTO [dbo].[StudentMark]([IdKindMark],[IdStudent],[Mark],[IsDebt])VALUES(1,9,6.4,0)
INSERT INTO [dbo].[StudentMark]([IdKindMark],[IdStudent],[Mark],[IsDebt])VALUES(1,10,7.4,0)
INSERT INTO [dbo].[StudentMark]([IdKindMark],[IdStudent],[Mark],[IsDebt])VALUES(1,11,6.3,0)
INSERT INTO [dbo].[StudentMark]([IdKindMark],[IdStudent],[Mark],[IsDebt])VALUES(1,12,7.1,0)
INSERT INTO [dbo].[StudentMark]([IdKindMark],[IdStudent],[Mark],[IsDebt])VALUES(1,13,5.4,0)
INSERT INTO [dbo].[StudentMark]([IdKindMark],[IdStudent],[Mark],[IsDebt])VALUES(1,14,6.9,0)
INSERT INTO [dbo].[StudentMark]([IdKindMark],[IdStudent],[Mark],[IsDebt])VALUES(1,15,7.9,0)
INSERT INTO [dbo].[StudentMark]([IdKindMark],[IdStudent],[Mark],[IsDebt])VALUES(1,16,6.8,0)
INSERT INTO [dbo].[StudentMark]([IdKindMark],[IdStudent],[Mark],[IsDebt])VALUES(1,17,7.0,0)
INSERT INTO [dbo].[StudentMark]([IdKindMark],[IdStudent],[Mark],[IsDebt])VALUES(1,18,5.6,0)
INSERT INTO [dbo].[StudentMark]([IdKindMark],[IdStudent],[Mark],[IsDebt])VALUES(1,19,6.4,0)
INSERT INTO [dbo].[StudentMark]([IdKindMark],[IdStudent],[Mark],[IsDebt])VALUES(1,20,7.4,0)
INSERT INTO [dbo].[StudentMark]([IdKindMark],[IdStudent],[Mark],[IsDebt])VALUES(1,21,6.1,0)
INSERT INTO [dbo].[StudentMark]([IdKindMark],[IdStudent],[Mark],[IsDebt])VALUES(1,22,6.7,0)
INSERT INTO [dbo].[StudentMark]([IdKindMark],[IdStudent],[Mark],[IsDebt])VALUES(1,23,5.6,0)
INSERT INTO [dbo].[StudentMark]([IdKindMark],[IdStudent],[Mark],[IsDebt])VALUES(1,24,6.4,0)
INSERT INTO [dbo].[StudentMark]([IdKindMark],[IdStudent],[Mark],[IsDebt])VALUES(1,25,7.4,0)
INSERT INTO [dbo].[StudentMark]([IdKindMark],[IdStudent],[Mark],[IsDebt])VALUES(1,26,6.9,0)
INSERT INTO [dbo].[StudentMark]([IdKindMark],[IdStudent],[Mark],[IsDebt])VALUES(1,27,7.6,0)
INSERT INTO [dbo].[StudentMark]([IdKindMark],[IdStudent],[Mark],[IsDebt])VALUES(1,28,5.8,0)
INSERT INTO [dbo].[StudentMark]([IdKindMark],[IdStudent],[Mark],[IsDebt])VALUES(1,29,6.3,0)
INSERT INTO [dbo].[StudentMark]([IdKindMark],[IdStudent],[Mark],[IsDebt])VALUES(1,30,7.1,0)
INSERT INTO [dbo].[StudentMark]([IdKindMark],[IdStudent],[Mark],[IsDebt])VALUES(1,31,6.6,1)
INSERT INTO [dbo].[StudentMark]([IdKindMark],[IdStudent],[Mark],[IsDebt])VALUES(1,32,3.7,1)
INSERT INTO [dbo].[StudentMark]([IdKindMark],[IdStudent],[Mark],[IsDebt])VALUES(1,33,5.4,0)
INSERT INTO [dbo].[StudentMark]([IdKindMark],[IdStudent],[Mark],[IsDebt])VALUES(1,34,6.7,0)
INSERT INTO [dbo].[StudentMark]([IdKindMark],[IdStudent],[Mark],[IsDebt])VALUES(1,35,7.1,0)
INSERT INTO [dbo].[StudentMark]([IdKindMark],[IdStudent],[Mark],[IsDebt])VALUES(1,36,6.7,0)
INSERT INTO [dbo].[StudentMark]([IdKindMark],[IdStudent],[Mark],[IsDebt])VALUES(1,37,7.9,0)
INSERT INTO [dbo].[StudentMark]([IdKindMark],[IdStudent],[Mark],[IsDebt])VALUES(1,38,5.2,0)
INSERT INTO [dbo].[StudentMark]([IdKindMark],[IdStudent],[Mark],[IsDebt])VALUES(1,39,6.3,0)
INSERT INTO [dbo].[StudentMark]([IdKindMark],[IdStudent],[Mark],[IsDebt])VALUES(1,40,7.8,0)
";
}
}
